package cn.yi.newsys.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.yi.newsys.dao.UserDao;
import cn.yi.newsys.pojo.User;
import cn.yi.newsys.utils.BaseDao;

public class UserDaoImpl extends BaseDao implements UserDao{

	@Override
	public int save(User user) throws Exception {
		String sql = "INSERT INTO news_users(username,userpass,usertype,email) VALUES(?,?,?,?)" ;
		Object objs [] = new Object[]{user.getUserName(),user.getUserPass(),user.getType(),user.getEmail()} ;
		return this.executeUpdate(sql, objs) ;
	}

	@Override
	public User getUserByName(User user) throws Exception {
		ResultSet rs = null ;
		User temp = null ;
		String sql = "SELECT uid,username,userpass,email,usertype FROM news_users WHERE userName=?" ;
		Object objs [] = new Object[]{user.getUserName()} ;
		try{
			rs = this.executeQuery(sql, objs) ;
			if(rs.next()){
				temp = new User() ;
				temp.setEmail(rs.getString("email")) ;
				temp.setId(rs.getInt("uid")) ;
				temp.setType(rs.getInt("usertype")) ;
				temp.setUserPass(rs.getString("userpass")) ;
				temp.setUserName(rs.getString("username")) ;
			}
		}finally{
			this.close() ;
		}
		return temp ;
	}

	@Override
	public List<User> getAllUser() {
		List<User> list = new ArrayList<User>() ;
		String sql = "SELECT uid,username,userpass,email,usertype FROM news_users ORDER BY uid DESC" ;
		ResultSet rs = null ;
		User user = null ;
		try{
			rs = this.executeQuery(sql, null) ;
			while(rs != null && rs.next()){
				user = new User() ;
				user.setEmail(rs.getString("email")) ;
				user.setId(rs.getInt("uid")) ;
				user.setType(rs.getInt("usertype")) ;
				user.setUserPass(rs.getString("userpass")) ;
				user.setUserName(rs.getString("username")) ;
				list.add(user) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close() ;
		}
		return list;
	}

	@Override
	public List<User> queryAllUser(int offset, int pageSize) {
		String sql = "SELECT uid,username,userpass,email,usertype FROM news_users ORDER BY uid DESC LIMIT ?,?" ;
		Object objs [] = new Object[]{offset,pageSize} ;
		List<User> list = new ArrayList<User>();
		User user = null ;
		try{
			ResultSet rs = this.executeQuery(sql, objs) ;
			while(rs != null && rs.next()){
				user = new User() ;
				user.setEmail(rs.getString("email")) ;
				user.setId(rs.getInt("uid")) ;
				user.setType(rs.getInt("usertype")) ;
				user.setUserPass(rs.getString("userpass")) ;
				user.setUserName(rs.getString("username")) ;
				list.add(user) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close();
		}
		return list ;
	}

	@Override
	public int queryTotalCount(String userName, Integer roleId) {
		int row = 0 ;
		StringBuffer sql = new StringBuffer() ;
		List<Object> parameters = new ArrayList<Object>() ;
		sql.append("SELECT COUNT(*) FROM news_users WHERE 1=1 ") ;
		if(userName != null && userName.length() > 0){
			sql.append("AND username LIKE ? ") ;
			parameters.add("%"+userName+"%") ;
		}
		if(roleId != null && roleId >= 0){
			sql.append("AND usertype=? ") ;
			parameters.add(roleId) ;
		}
		try{
			ResultSet rs = this.executeQuery(sql.toString(), parameters.toArray()) ;
			if(rs != null && rs.next()){
				row = rs.getInt(1) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close() ;
		}
		return row ;
	}

	@Override
	public List<User> queryUserByPageNo(int offset, int pageSize,
			String userName, Integer roleId) {
		// 用于动态创建 SQL 语句
		StringBuffer sql = new StringBuffer() ;
		sql.append("SELECT uid,username,userpass,email,usertype FROM news_users WHERE 1=1 ") ;
		// 用于返回用户集合
		List<User> list = new ArrayList<User>() ;
		// 用于动态 设置参数
		List<Object> parameters = new ArrayList<Object>() ;
		User user = null ;
		if(userName != null && userName.length() > 0){
			sql.append("AND username LIKE ? ") ;
			parameters.add("%"+userName+"%") ;
		}
		if(roleId != null && roleId >= 0){
			sql.append("AND usertype=? ");
			parameters.add(roleId) ;
		}
		sql.append("ORDER BY uid DESC LIMIT ?,?") ;
		parameters.add(offset) ;
		parameters.add(pageSize) ;
		// 执行 SQL 语句
		ResultSet rs = this.executeQuery(sql.toString(), parameters.toArray()) ;
		// 判断 结果集是否为空 且有结果
		try {
			while(rs != null && rs.next()){
				user = new User() ;
				user.setEmail(rs.getString("email")) ;
				user.setId(rs.getInt("uid")) ;
				user.setType(rs.getInt("usertype")) ;
				user.setUserPass(rs.getString("userpass")) ;
				user.setUserName(rs.getString("username")) ;
				list.add(user) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close() ;
		}
		return list ;
	}

	@Override
	public User queryUserByEmail(String email) {
		ResultSet rs = null ;
		User temp = null ;
		String sql = "SELECT uid,username,userpass,email,usertype FROM news_users WHERE email=?" ;
		Object objs [] = new Object[]{email} ;
		try{
			rs = this.executeQuery(sql, objs) ;
			if(rs.next()){
				temp = new User() ;
				temp.setEmail(rs.getString("email")) ;
				temp.setId(rs.getInt("uid")) ;
				temp.setType(rs.getInt("usertype")) ;
				temp.setUserPass(rs.getString("userpass")) ;
				temp.setUserName(rs.getString("username")) ;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.close() ;
		}
		return temp ;
	}


}
